package org.example.mybatis.mapper;

import org.apache.ibatis.annotations.*;
import org.example.mybatis.model.UserInfo;

import java.util.List;

@Mapper
public interface UserInfoMapper {

    @Select("select * from userinfo")
    List<UserInfo> queryUserInfoList();

    // 通过 @Param 给参数起别名
    @Select("select * from userinfo where id = #{id}")
    UserInfo queryUserInfoById(@Param("id") Integer id);

    @Select("select * from userinfo where username=#{username}")
    UserInfo queryUserInfoByUsername(@Param("username") String username);

    @Select("select * from userinfo where id=${id}")
    UserInfo queryUserInfoById2(Integer id);

    // 如果参数为字符串，使用 ${} 时需要加引号 -> '${}'
    @Select("select * from userinfo where username='${username}'")
    UserInfo queryUserInfoByUsername2(@Param("username") String username);

    // 通常情况下，我们把 @Param 注解中的别名 和 参数名保持一致
    @Select("select * from userinfo where id = #{id} and delete_flag = #{deleteFlag}")
    UserInfo queryUserInfoByIdAndDF(@Param("id") Integer id,@Param("deleteFlag") Integer deleteFlag);

    // 参数为对象  可以直接在 sql 中写对象的属性
    @Insert("insert into userinfo(username, password, age, gender, phone) values (#{username}, #{password}, #{age}, #{gender}, #{phone})")
    Integer insertUserInfo(UserInfo userInfo);

    // 参数为对象，使用 @Param 注解时，sql语句中 要指定 对象.属性
    @Insert("insert into userinfo(username, password, age, gender, phone) values (#{userInfo.username}, #{userInfo.password}, #{userInfo.age}, #{userInfo.gender}, #{userInfo.phone})")
    Integer insertUserInfoParam(@Param("userInfo") UserInfo userInfo);

    // @Option 注解用来获取自增主键 keyProperty 是自增主键对应的属性
    @Options(useGeneratedKeys = true, keyProperty = "id")
    @Insert("insert into userinfo(username, password, age, gender, phone) values(#{username}, #{password}, #{age}, #{gender}, #{phone})")
    Integer insertGetKey(UserInfo userInfo);

    @Delete("delete from userinfo where id = #{id}")
    Integer deleteUserInfoById(Integer id);

    @Update("update userinfo set age=#{age}, gender=#{gender}, phone=#{phone} where id=#{id}")
    Integer updateUserInfo(UserInfo userInfo);


    // 当数据库中的字段名称和类的属性名称不一致时，查询不到对应属性的信息 (为 null)
    @Select("select id, username, password, age, gender, phone, delete_flag, create_time, update_time from userinfo")
    List<UserInfo> queryAllUserInfo();

    // 1. 第一种解决方法 设置别名
    @Select("select id, username, password, age, gender, phone, delete_flag as deleteFlag, create_time as createTime, update_time as updateTime from userinfo")
    List<UserInfo> queryAllUserInfo1();

    // 2. 第二种解决方法 使用 @Results 和 @Result 注解 设置结果映射
    // 如果其他方法想要复用
    @Results(id = "baseQuery", value = {
            @Result(column = "delete_flag", property = "deleteFlag"),
            @Result(column = "create_time", property = "createTime"),
            @Result(column = "update_time", property = "updateTime")
    })
    @Select("select id, username, password, age, gender, phone, delete_flag, create_time, update_time from userinfo")
    List<UserInfo> queryAllUserInfo2();

    // 这样每次查都需要写一遍太麻烦了
//    @Results({
//            @Result(column = "delete_flag", property = "deleteFlag"),
//            @Result(column = "create_time", property = "createTime"),
//            @Result(column = "update_time", property = "updateTime")
//    })
    // 想要复用刚才写的结果映射可以使用 @ResultMap 注解
    // 前提是 要给刚刚的结果映射 起一个别名 @Results(id = "baseQuery", value ={...})
    @ResultMap("baseQuery")
    @Select("select id, username, password, age, gender, phone, delete_flag, create_time, update_time from userinfo where id=#{id}")
    UserInfo queryAllUserInfo3();

    // 3. 这两种方法都可以，但是还是太麻烦了 此时就可以使用 配置文件的方式 设置自动驼峰转换
    @Select("select id, username, password, age, gender, phone, delete_flag, create_time, update_time from userinfo")
    List<UserInfo> queryAllUserInfo4();

//    @Select("select * from userinfo where username=#{username} and password=#{password}")
    @Select("select * from userinfo where username='${username}' and password='${password}'")
    List<UserInfo> queryUserInfoByUsernameAndPassword(@Param("username") String username, @Param("password") String password);

    // 这里只能使用 ${} 因为关键字不需要加''
    @Select("select * from userinfo order by id ${order}")
    List<UserInfo> queryUserByOrder(String order);

    // 使用 $ 存在SQL注入问题
//    @Select("select * from userinfo where username like '%${username}%'")
    @Select("select * from userinfo where username like concat('%',#{username},'%')")
    List<UserInfo> queryUserByLike(String username);

    @Delete("<script>" +
            "delete from userinfo where id in " +
            "<foreach collection='ids' separator=',' open='(' close=')' item='id'> " +
            "#{id} " +
            "</foreach> " +
            "</script> ")
    Integer batchDelete(List<Integer> ids);
}
